/**
 * Session Manager with SQLite Persistence
 * 
 * Manages chat sessions with persistent storage using Bun SQLite
 */

import { Database } from 'bun:sqlite';
import type { Session, Message } from '@/types';
import { logger } from '@/utils/logger';
import path from 'path';

export interface SessionListOptions {
  limit?: number;
  offset?: number;
  sortBy?: 'createdAt' | 'updatedAt';
  sortOrder?: 'asc' | 'desc';
  search?: string | undefined;
}

export interface SessionStats {
  total: number;
  active: number;
  archived: number;
}

/**
 * SessionManager class for managing chat sessions with SQLite
 */
export class SessionManager {
  private db: Database;
  private static instance: SessionManager | null = null;

  private constructor(dbPath: string = './data/sessions.db') {
    // Ensure data directory exists
    const dir = path.dirname(dbPath);
    const fs = require('fs');
    if (!fs.existsSync(dir)) {
      fs.mkdirSync(dir, { recursive: true });
    }

    // Open database
    this.db = new Database(dbPath, { create: true });

    // Initialize schema
    this.initializeSchema();

    logger.info('SessionManager initialized', { dbPath });
  }

  /**
   * Get singleton instance
   */
  public static getInstance(dbPath?: string): SessionManager {
    if (!SessionManager.instance) {
      SessionManager.instance = new SessionManager(dbPath);
    }
    return SessionManager.instance;
  }

  /**
   * Initialize database schema
   */
  private initializeSchema(): void {
    // Sessions table
    this.db.run(`
      CREATE TABLE IF NOT EXISTS sessions (
        id TEXT PRIMARY KEY,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        agent_options TEXT NOT NULL,
        metadata TEXT,
        archived INTEGER DEFAULT 0,
        title TEXT,
        sub_agent_id TEXT
      )
    `);

    // Messages table
    this.db.run(`
      CREATE TABLE IF NOT EXISTS messages (
        id TEXT PRIMARY KEY,
        session_id TEXT NOT NULL,
        role TEXT NOT NULL,
        content TEXT NOT NULL,
        timestamp INTEGER NOT NULL,
        metadata TEXT,
        sub_agent_id TEXT,
        FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
      )
    `);

    // Create indexes
    this.db.run(`CREATE INDEX IF NOT EXISTS idx_sessions_updated_at ON sessions(updated_at DESC)`);
    this.db.run(`CREATE INDEX IF NOT EXISTS idx_sessions_created_at ON sessions(created_at DESC)`);
    this.db.run(`CREATE INDEX IF NOT EXISTS idx_sessions_archived ON sessions(archived)`);
    this.db.run(`CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages(session_id)`);
    this.db.run(`CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON messages(timestamp)`);

    logger.info('Database schema initialized');
  }

  /**
   * Create a new session
   */
  public createSession(
    agentOptions: any = {},
    metadata: any = {},
    title?: string,
    subAgentId?: string
  ): Session {
    const sessionId = this.generateSessionId();
    const now = Date.now();

    const stmt = this.db.prepare(`
      INSERT INTO sessions (id, created_at, updated_at, agent_options, metadata, title, sub_agent_id)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      sessionId,
      now,
      now,
      JSON.stringify(agentOptions),
      JSON.stringify(metadata),
      title || null,
      subAgentId || null
    );

    logger.info('Session created', { sessionId, title, subAgentId });

    return {
      id: sessionId,
      createdAt: new Date(now),
      updatedAt: new Date(now),
      agentOptions,
      messages: [],
      metadata,
    };
  }

  /**
   * Get session by ID
   */
  public getSession(sessionId: string): Session | null {
    const stmt = this.db.prepare(`
      SELECT * FROM sessions WHERE id = ?
    `);

    const row = stmt.get(sessionId) as any;

    if (!row) {
      return null;
    }

    // Get messages for this session
    const messages = this.getSessionMessages(sessionId);

    return {
      id: row.id,
      createdAt: new Date(row.created_at),
      updatedAt: new Date(row.updated_at),
      agentOptions: JSON.parse(row.agent_options),
      messages,
      metadata: row.metadata ? JSON.parse(row.metadata) : {},
    };
  }

  /**
   * Get messages for a session
   */
  public getSessionMessages(sessionId: string): Message[] {
    const stmt = this.db.prepare(`
      SELECT * FROM messages WHERE session_id = ? ORDER BY timestamp ASC
    `);

    const rows = stmt.all(sessionId) as any[];

    return rows.map(row => ({
      id: row.id,
      role: row.role,
      content: row.content,
      timestamp: new Date(row.timestamp),
      metadata: row.metadata ? JSON.parse(row.metadata) : {},
    }));
  }

  /**
   * Add message to session
   */
  public addMessage(
    sessionId: string,
    role: 'user' | 'assistant' | 'system',
    content: string,
    metadata: any = {},
    subAgentId?: string
  ): Message {
    const messageId = this.generateMessageId();
    const now = Date.now();

    const stmt = this.db.prepare(`
      INSERT INTO messages (id, session_id, role, content, timestamp, metadata, sub_agent_id)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      messageId,
      sessionId,
      role,
      content,
      now,
      JSON.stringify(metadata),
      subAgentId || null
    );

    // Update session updated_at
    this.updateSessionTimestamp(sessionId);

    return {
      id: messageId,
      role,
      content,
      timestamp: new Date(now),
      metadata,
    };
  }

  /**
   * Update session timestamp
   */
  private updateSessionTimestamp(sessionId: string): void {
    const stmt = this.db.prepare(`
      UPDATE sessions SET updated_at = ? WHERE id = ?
    `);

    stmt.run(Date.now(), sessionId);
  }

  /**
   * List sessions with pagination and filtering
   */
  public listSessions(options: SessionListOptions = {}): {
    sessions: Session[];
    total: number;
    hasMore: boolean;
  } {
    const {
      limit = 20,
      offset = 0,
      sortBy = 'updatedAt',
      sortOrder = 'desc',
      search,
    } = options;

    const sortColumn = sortBy === 'createdAt' ? 'created_at' : 'updated_at';
    const order = sortOrder.toUpperCase();

    let query = `SELECT * FROM sessions WHERE archived = 0`;
    const params: any[] = [];

    // Add search filter
    if (search) {
      query += ` AND (title LIKE ? OR metadata LIKE ?)`;
      params.push(`%${search}%`, `%${search}%`);
    }

    // Add sorting
    query += ` ORDER BY ${sortColumn} ${order}`;

    // Add pagination
    query += ` LIMIT ? OFFSET ?`;
    params.push(limit, offset);

    const stmt = this.db.prepare(query);
    const rows = stmt.all(...params) as any[];

    // Get total count
    let countQuery = `SELECT COUNT(*) as count FROM sessions WHERE archived = 0`;
    const countParams: any[] = [];

    if (search) {
      countQuery += ` AND (title LIKE ? OR metadata LIKE ?)`;
      countParams.push(`%${search}%`, `%${search}%`);
    }

    const countStmt = this.db.prepare(countQuery);
    const countRow = countStmt.get(...countParams) as any;
    const total = countRow.count;

    const sessions = rows.map(row => ({
      id: row.id,
      createdAt: new Date(row.created_at),
      updatedAt: new Date(row.updated_at),
      agentOptions: JSON.parse(row.agent_options),
      messages: [], // Don't load messages for list view
      metadata: row.metadata ? JSON.parse(row.metadata) : {},
    }));

    return {
      sessions,
      total,
      hasMore: offset + limit < total,
    };
  }

  /**
   * Delete session
   */
  public deleteSession(sessionId: string): boolean {
    const stmt = this.db.prepare(`DELETE FROM sessions WHERE id = ?`);
    const result = stmt.run(sessionId);

    if (result.changes > 0) {
      logger.info('Session deleted', { sessionId });
      return true;
    }

    return false;
  }

  /**
   * Archive session
   */
  public archiveSession(sessionId: string): boolean {
    const stmt = this.db.prepare(`UPDATE sessions SET archived = 1 WHERE id = ?`);
    const result = stmt.run(sessionId);

    return result.changes > 0;
  }

  /**
   * Get session statistics
   */
  public getStats(): SessionStats {
    const totalStmt = this.db.prepare(`SELECT COUNT(*) as count FROM sessions`);
    const activeStmt = this.db.prepare(`SELECT COUNT(*) as count FROM sessions WHERE archived = 0`);
    const archivedStmt = this.db.prepare(`SELECT COUNT(*) as count FROM sessions WHERE archived = 1`);

    const total = (totalStmt.get() as any).count;
    const active = (activeStmt.get() as any).count;
    const archived = (archivedStmt.get() as any).count;

    return { total, active, archived };
  }

  /**
   * Generate unique session ID
   */
  private generateSessionId(): string {
    return `session_${Date.now()}_${Math.random().toString(36).substring(2, 15)}`;
  }

  /**
   * Generate unique message ID
   */
  private generateMessageId(): string {
    return `msg_${Date.now()}_${Math.random().toString(36).substring(2, 15)}`;
  }

  /**
   * Close database connection
   */
  public close(): void {
    this.db.close();
    logger.info('SessionManager closed');
  }
}

/**
 * Get singleton instance
 */
export function getSessionManager(dbPath?: string): SessionManager {
  return SessionManager.getInstance(dbPath);
}

